package main

import (
	"log"

	"github.com/jmoiron/sqlx"

	_ "github.com/go-sql-driver/mysql"
)

func init() {
	// 连接数据库
	conn, err := sqlx.Open("mysql", "root:vader20011014@(localhost:3306)/study?parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	err = conn.Ping()
	if err != nil {
		log.Fatal(err)
	}
	db = conn
}

var db *sqlx.DB

// 定义数据结构
type Person struct {
	UserId   string `db:"id"`
	Username string `db:"name"`
	Age      int    `db:"age"`
	Address  string `db:"address"`
}

// -----查询-----
func query() {
	var p Person
	query := `SELECT * FROM user WHERE id = ?`
	err := db.Get(&p, query, "12132")
	if err != nil {
		log.Fatal(err)
		return
	}
	log.Printf("query succ: %v\n", p)
}

func list() {
	var ps []Person
	query := `SELECT * FROM user`
	err := db.Select(&ps, query)
	if err != nil {
		log.Fatal(err)
	}
	for _, p := range ps {
		log.Printf("query succ: %v\n", p)
	}
}

// -----新增-----
func insert() {
	query := `INSERT INTO user VALUES (?,?,?,?)`
	res, err := db.Exec(query, "1145", "alan", 24, "China")
	if err != nil {
		log.Fatal(err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		log.Fatal(err)
		return
	}
	log.Println("insert succ:", id)
}

// -----更新-----
func update() {
	query := `UPDATE user SET NAME = ? WHERE ID = ?`
	res, err := db.Exec(query, "alan223", 114514)
	if err != nil {
		log.Fatal(err)
		return
	}
	eff, err := res.RowsAffected()
	if err != nil || eff == 0 {
		log.Fatal(err)
		return
	}
	log.Println("update succ")
}

// -----删除-----
func delete() {
	query := `DELETE FROM user WHERE id = ?`
	res, err := db.Exec(query, "1145")
	if err != nil {
		log.Fatal(err)
		return
	}
	eff, err := res.RowsAffected()
	if err != nil || eff == 0 {
		log.Fatal(err)
		return
	}
	log.Println("delete succ")
}

// -----事物-----
func tx() {
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	defer tx.Rollback()

	// 事务本体：各种操作
	// query()
	// insert()
	// update()
	// delete()
	// list()

	tx.Commit()
}

func main() {
	query()
	// insert()
	// update()
	// delete()
	list()
	go tx()
}
